package com.HRs.Dao;

import com.HRs.utils.Account;
import com.HRs.domain.Dtadministrator;
import com.HRs.domain.Patient;
import com.HRs.domain.Visit;
import com.HRs.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class dtADaolmpl implements dtADao{
    @Override
    public boolean Login_dtA(String Id, String Password) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "select dtA_Password from dtadministrator_info where dtA_Id = '"+Id+"'";
            rs = st.executeQuery(sql);
            String password = "";
            while(rs.next()){
                password = rs.getString("dtA_Password");
            }
            if(password.equals("")){
                return false;
            }else return password.equals(Password);

        }catch (Exception e){
            e.printStackTrace();
            return false;
        }finally {
            JDBCUtils.close(con, st, rs);
        }
    }

    @Override
    public ArrayList<Dtadministrator> get_Dta() {
        ArrayList<Dtadministrator> dtA = new ArrayList<>();

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "select * from dtadministrator_info";
            rs = st.executeQuery(sql);
            String dtA_Id = "";
            String dtA_Name = "";
            String dtA_Department = "";
            String dtA_Sex = "";
            String dtA_ContactNumber = "";
            String dtA_Password = "";

            while(rs.next()){
                dtA_Name = rs.getString("dtA_Name");
                dtA_Id = rs.getString("dtA_Id");
                dtA_Department = rs.getString("dtA_Department");
                dtA_Sex = rs.getString("dtA_Sex");
                dtA_ContactNumber = rs.getString("dtA_ContactNumber");
                dtA_Password = rs.getString("dtA_Password");
                dtA.add(new Dtadministrator(dtA_Id, dtA_Name, dtA_Department, dtA_Sex, dtA_ContactNumber, dtA_Password));
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }

        return dtA;
    }

    @Override
    public void delete_row(int row) {
        Integer r = row;
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "call delete_row(" +  r.toString() + ")";
            rs = st.executeQuery(sql);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }

    }

    @Override
    public void insert(Dtadministrator dta) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "insert into dtadministrator_info(dtA_Id, dtA_Name, dtA_Department, dtA_Sex, dtA_ContactNumber, dtA_Password) VALUE\n" +
                    "('"+dta.getDta_Id()+"', '"+dta.getDta_Name()+"', '"+dta.getDta_Department()+"', '"+dta.getDta_Sex()+"', '"+dta.getDta_ContactNumber()+"', '"+dta.getDta_Password()+"');";
            st.execute(sql);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }
    }

    @Override
    public void delete_v(String ID, String info) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "delete from visit_info where " + ID + " = '"+info+"'";
            st.execute(sql);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }
    }

    @Override
    public void insert_v(Visit v, String id) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "insert into visit_info(v_Id, v_Time, v_MaxNum, v_Department) VALUE ('"+v.getV_Id()+"','"+v.getV_Time()+"','"+v.getV_MaxNum()+"','"+v.getV_Department()+"')";
            st.execute(sql);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }
    }

    @Override
    public ArrayList<Visit> get_V(String id) {
        ArrayList<Visit> Vit = new ArrayList<Visit>();

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "select v_Id,v_Time,v_MaxNum,v_Department from visit_info\n" +
                    "where v_Department = (select dtA_Department from dtadministrator_info where dtA_Id = '"+id+"');";
            rs = st.executeQuery(sql);
            String v_Id = "";
            String v_Time = "";
            String v_MaxNum = "";
            String v_Department = "";

            while(rs.next()){
                v_Id = rs.getString("v_Id");
                v_Time = rs.getString("v_Time");
                v_MaxNum = rs.getString("v_MaxNum");
                v_Department = rs.getString("v_Department");

                Vit.add(new Visit(v_Id, v_Time, v_MaxNum, v_Department));
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }

        return Vit;
    }

    @Override
    public ArrayList<Patient> get_P(String Date){
        ArrayList<Patient> list_p = new ArrayList<>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "select * from patient_info\n" +
                    "where p_Pid in (select po_Pid from patientorder_info\n" +
                    "    where po_Date = '"+Date+"' and po_Department in (select dtA_Department from dtadministrator_info\n" +
                    "        where dtA_Id = '"+ Account.dta_id +"'));";
            rs = st.executeQuery(sql);
            String p_Pid = "";
            String p_Name = "";
            String p_Password = "";
            String p_Sex = "";
            String p_PhoneNumber = "";
            String p_Address = "";
            String p_ID = "";

            while(rs.next()){
                p_Pid = rs.getString("p_Pid");
                p_Name = rs.getString("p_Name");
                p_Password = rs.getString("p_Password");
                p_Sex = rs.getString("p_Sex");
                p_PhoneNumber = rs.getString("p_PhoneNumber");
                p_Address = rs.getString("p_Address");
                p_ID = rs.getString("p_ID");
                list_p.add(new Patient(p_Pid, p_Name, p_Password, p_Sex, p_PhoneNumber, p_Address,p_ID));
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }

        return list_p;
    }

    @Override
    public int count_OdNum(String id) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        int num = 0;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "call count_OdNum('"+id+"')";
            rs = st.executeQuery(sql);
            while(rs.next()){
                num = rs.getInt("sum");

//                Vit.add(new Visit(v_Id, v_Time, v_MaxNum, v_Department));
            }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }
        return num;
    }

    @Override
    public void flush_(String dt) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "call flush_DocOdInfo_Zd('"+dt+"')";
            st.execute(sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con, st, rs);
        }
    }

    public static void main(String[] args) {
        dtADaolmpl dD = new dtADaolmpl();
        System.out.println(dD.count_OdNum("1001"));
    }

}
